package com.thx.common.plug.excel;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;


/**
 * excel工具类，提供导入导出方法
 *
 * @author 陈上远
 *
 */
public class ExcelUtil {

	/**
	 * 实现了通过映射关系，由实体数据集或者数组数据集到Excel表数据的转换
	 *
	 * @param xmlFileName
	 *            xml名字
	 * @param dataSet
	 *            实体数据 或 数组数据集合
	 * @param out
	 *            输出流
	 *
	 * @param excleExpandName
	 *            控制导出版本 ,Excel扩展名:xls/xlsx
	 *
	 */
	public static void exportExcel(String xmlFileName,
			List<? extends Object> dataSet, OutputStream out,
			String excleExpandName) {
		exportExcel(xmlFileName, dataSet, out, excleExpandName, null,null);
	}

	/**
	 * 可自定义sheetName,sheetTitle
	 *
	 * @param xmlFileName
	 * @param dataSet
	 * @param out
	 * @param excleExpandName
	 * @param sheetName
	 * @param sheetTitle
	 */
	public static void exportExcel(String xmlFileName,
			List<? extends Object> dataSet, OutputStream out,
			String excleExpandName, String sheetName,String sheetTitle) {
		exportExcel(xmlFileName, dataSet, out, excleExpandName, sheetName,sheetTitle, null);
	}

	/**
	 * 导出数据有图片并且用到资源管理模块时,可结合资源管理模块进行导出
	 *
	 * @param xmlFileName
	 * @param dataSet
	 * @param out
	 * @param excleExpandName
	 * @param sheetName
	 * @param sheetTitle
	 * @param extend
	 *            Excel扩展接口,可结合资源管理模块进行图片导出
	 */
	public static void exportExcel(String xmlFileName,
			List<? extends Object> dataSet, OutputStream out,
			String excleExpandName, String sheetName,String sheetTitle, ExcelExtends extend) {
		Element root = getXmlRoot(xmlFileName);
		if (root != null && out != null) {
			try {
				ExportXmlDataEntity exdt = getExpXmlDataEntity(root);
				List<ExcelColumnEntity> columnList = getExlColEntitys(root);
				Workbook wb = getWorkbook(excleExpandName);
				Sheet sheet;
				if (StringUtils.isNotBlank(sheetName)) {
					sheet = wb.createSheet(sheetName);
				} else {
					sheet = wb.createSheet();
				}
				writeExpTitle(exdt, columnList, wb, sheet,sheetTitle);
				writeExpHeader(exdt, columnList, wb, sheet,sheetTitle);
				writeExpDate(dataSet, exdt, columnList, wb, sheet,sheetTitle, extend);
				wb.write(out);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	/**
	 * 实现了通过映射关系，由Excel表数据到实体数据集或数组数据集 的转换
	 *
	 * @param xmlFileName
	 * @param in
	 * @param excleExpandName
	 * @return
	 */
	public static ImportResult importExcel(String xmlFileName, InputStream in,
			String excleExpandName) {
		try {
			Element root = getXmlRoot(xmlFileName);
			if (root != null && in != null) {
				ImportXmlDataEntity imdt = getImpXmlDataEntity(root);
				List<ExcelColumnEntity> columnList = getExlColEntitys(root);
				Workbook wb;
				if ("xls".equals(excleExpandName)) {
					wb = new HSSFWorkbook(in);
				} else {
					wb = new XSSFWorkbook(in);
				}
				Sheet sheet = wb.getSheetAt(0);
				return getImpResult(imdt, columnList, sheet);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 获取的导入模板
	 *
	 * @param xmlFileName
	 * @param out
	 * @param excleExpandName
	 */
	public static void getImpTemplate(String xmlFileName, OutputStream out,
			String excleExpandName) {
		Element root = getXmlRoot(xmlFileName);
		if (root != null && out != null) {
			List<ExcelColumnEntity> columnList = getExlColEntitys(root);
			if (columnList != null && columnList.size() > 0) {
				Workbook wb = getWorkbook(excleExpandName);
				Sheet sheet = wb.createSheet();
				CellStyle style = getExpHeaderStyle(wb, sheet);
				Row row = sheet.createRow(0);
				Cell cell;
				for (int i = 0; i < columnList.size(); i++) {
					cell = row.createCell(i);
					cell.setCellStyle(style);
					cell.setCellValue(columnList.get(i).getHeader());
				}
				try {
					wb.write(out);
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 获取xml文件根节点
	 *
	 * @param xmlFileName
	 *            xml文件名
	 * @return
	 */
	private static Element getXmlRoot(String xmlFileName) {
		if (StringUtils.isNotBlank(xmlFileName)) {
			InputStream in = ExcelUtil.class.getClassLoader()
					.getResourceAsStream(xmlFileName);
			SAXReader reader = new SAXReader();
			reader.setValidation(false);
			Document doc;
			try {
				doc = reader.read(in);
				Element rootElement = doc.getRootElement();
				return rootElement;
			} catch (DocumentException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * 获取ExportXmlDataEntity对象，其中包含导出的Excel表标题信息
	 *
	 * @param e
	 *            xml元素结点
	 * @return
	 */
	private static ExportXmlDataEntity getExpXmlDataEntity(Element e) {
		try {
			ExportXmlDataEntity exdt = new ExportXmlDataEntity();
			Element title = e.element("Title");
			Element hasId = e.element("hasId");
			if (title != null) {
				exdt.setTitle(title.getText());
				String row = title.attributeValue("row");
				String col = title.attributeValue("col");
				if (StringUtils.isNotBlank(row)) {
					int rowNum = Integer.parseInt(row);
					if (rowNum > 0) {
						exdt.setTitleRow(rowNum - 1);
					}
				}
				if (StringUtils.isNotBlank(col)) {
					int colNum = Integer.parseInt(col);
					if (colNum > 0) {
						exdt.setTitleCol(colNum - 1);
					}
				}
			}
			if (hasId != null && !("".equals(hasId.getText()))) {
				exdt.setHasId(Boolean.parseBoolean(hasId.getText()));
			}
			return exdt;
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		return null;
	}

	/**
	 * 获取ImportXmlDataEntity对象，其中包含要导入的Excel表对应的实体类,验证类信息
	 *
	 * @param e
	 *            xml元素结点
	 * @return
	 */
	private static ImportXmlDataEntity getImpXmlDataEntity(Element e) {
		try {
			ImportXmlDataEntity imdt = new ImportXmlDataEntity();
			Element className = e.element("ClassName");
			Element checkClassName = e.element("CheckClassName");
			if (className != null && !("".equals(className.getText()))) {
				imdt.setClassname(className.getText());
			}
			if (checkClassName != null
					&& !("".equals(checkClassName.getText()))) {
				imdt.setCheckClassName(checkClassName.getText());
			}
			return imdt;
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		return null;
	}

	/**
	 * 获取ExcelColumnEntity集合，其中包含Excel表头与实体字段映射关系
	 *
	 * @param e
	 *            xml元素结点
	 * @return
	 */
	private static List<ExcelColumnEntity> getExlColEntitys(Element e) {
		Element ColumnsElement = e.element("Columns");
		if (ColumnsElement != null) {
			List<Element> Columns = ColumnsElement.elements("Column");
			if (Columns != null && Columns.size() > 0) {
				List<ExcelColumnEntity> columnList = new ArrayList<ExcelColumnEntity>();
				for (Element col : Columns) {
					ExcelColumnEntity excelColumn = new ExcelColumnEntity();
					Element header = col.element("Header");
					Element arrayIndex = col.element("ArrayIndex");
					Element entityField = col.element("EntityField");
					Element rltEntity = col.element("RltEntity");
					Element rltEntityField = col.element("RltEntityField");
					Element dicn = col.element("Dictionary");
					Element checks = col.element("Checks");
					if (header != null && !("".equals(header.getText()))) {
						excelColumn.setHeader(header.getText());
					}
					if (arrayIndex != null && !("".equals(arrayIndex.getText()))) {
						int index = Integer.parseInt(arrayIndex.getText());
						if(index > 0){
							excelColumn.setArrayIndex(index);
						}
					}
					if (entityField != null
							&& !("".equals(entityField.getText()))) {
						excelColumn.setEntityField(entityField.getText());
						String type = entityField.attributeValue("type");
						if (StringUtils.isNotBlank(type)
								&& "picture".equals(type)) {
							excelColumn.setIsPicture(1);
						} else {
							excelColumn.setIsPicture(0);
						}
					}
					if (rltEntity != null && !("".equals(rltEntity.getText()))) {
						excelColumn.setRltEntity(rltEntity.getText());
					}
					if (rltEntityField != null
							&& !("".equals(rltEntityField.getText()))) {
						excelColumn.setRltEntityField(rltEntityField.getText());
					}
					if (dicn != null) {
						List<Element> mappings = dicn.elements("Mapping");
						if (mappings != null && mappings.size() > 0) {
							Map<String, String> map = new HashMap<String, String>();
							for (int i = 0; i < mappings.size(); i++) {
								String cellValue = mappings.get(i).getText();
								String fieldValue = mappings.get(i)
										.attributeValue("fieldValue");
								if (StringUtils.isNotBlank(cellValue)
										&& StringUtils.isNotBlank(fieldValue)) {
									map.put(fieldValue, cellValue);
								}
							}
							excelColumn.setMapping(map);
						}
					}
					if (checks != null) {
						List<Element> checkElements = checks.elements("Check");
						if (checkElements != null && checkElements.size() > 0) {
							Map<String, Map<String, String>> checkMap = new HashMap<String, Map<String, String>>();
							int count=0;
							for (Element check : checkElements) {
								Element method = check.element("Method");
								Element params = check.element("Params");
								String methodName = null;
								Map<String, String> paramsMap = null;
								if (method != null
										&& !("".equals(method.getText()))) {
									methodName = method.getText();
								}
								if (params != null) {
									List<Element> paramElements = params
											.elements("Param");
									if (paramElements != null
											&& paramElements.size() > 0) {
										paramsMap = new HashMap<String, String>();
										int count1=0;
										for (Element param : paramElements) {
											String paramValue = param.getText();
											String paramType = param
													.attributeValue("type");
											if (StringUtils
													.isNotBlank(paramValue)
													&& StringUtils
															.isNotBlank(paramType)) {
												paramsMap.put(paramType+"@"+count1,
														paramValue);
											}
											count1++;
										}
									}
								}
								// 可以没参数
								if (methodName != null) {
									checkMap.put(methodName+"@"+count, paramsMap);
								}
								count++;
							}
							excelColumn.setCheckMap(checkMap);
						}
					}
					columnList.add(excelColumn);
				}
				return columnList;
			}
		}
		return null;
	}

	/**
	 * 由文件后缀名获取Workbook
	 *
	 * @param excleExpandName
	 * @return
	 */
	private static Workbook getWorkbook(String excleExpandName) {
		Workbook wb;
		if ("xls".equals(excleExpandName)) {
			wb = new HSSFWorkbook();
		} else {
			wb = new XSSFWorkbook();
		}
		return wb;
	}

	/**
	 * 写标题
	 * @param exdt
	 * @param columnList
	 * @param wb
	 * @param sheet
	 * @param sheetTitle
	 */
	private static void writeExpTitle(ExportXmlDataEntity exdt,
			List<ExcelColumnEntity> columnList, Workbook wb, Sheet sheet,String sheetTitle) {
		if(exdt!=null){
			if(StringUtils.isNotBlank(sheetTitle)||StringUtils.isNotBlank(exdt.getTitle())){
				int count = 0;
				if (columnList != null && columnList.size() > 0) {
					boolean hasId = exdt.getHasId();
					if (hasId) {
						count = columnList.size() + 1;
					} else {
						count = columnList.size();
					}
				}
				Row row = sheet.createRow(exdt.getTitleRow());
				Cell cell = row.createCell(exdt.getTitleCol());
				CellStyle style = getExpTitleStyle(wb, sheet, count, exdt);
				cell.setCellStyle(style);
				if(StringUtils.isNotBlank(sheetTitle)){
					cell.setCellValue(sheetTitle);
				}else{
					cell.setCellValue(exdt.getTitle());
				}
			}
		}
	}

	/**
	 * 写表头
	 *
	 * @param exdt
	 * @param columnList
	 * @param wb
	 * @param sheet
	 * @param sheetTitle
	 */
	private static void writeExpHeader(ExportXmlDataEntity exdt,
			List<ExcelColumnEntity> columnList, Workbook wb, Sheet sheet,String sheetTitle) {
		if (exdt != null && columnList != null && columnList.size() > 0) {
			Row row;
			Cell cell;
			CellStyle style = getExpHeaderStyle(wb, sheet);
			if (StringUtils.isNotBlank(sheetTitle)||StringUtils.isNotBlank(exdt.getTitle())) {
				row = sheet.createRow(exdt.getTitleRow() + 1);
			} else {
				row = sheet.createRow(exdt.getTitleRow());
			}
			// 判断表头有无序号
			if (exdt.getHasId()) {
				cell = row.createCell(exdt.getTitleCol());
				cell.setCellStyle(style);
				cell.setCellValue("序号");
				for (int i = 0; i < columnList.size(); i++) {
					cell = row.createCell(exdt.getTitleCol() + 1 + i);
					cell.setCellStyle(style);
					cell.setCellValue(columnList.get(i).getHeader());
				}
			} else {
				for (int i = 0; i < columnList.size(); i++) {
					cell = row.createCell(exdt.getTitleCol() + i);
					cell.setCellStyle(style);
					cell.setCellValue(columnList.get(i).getHeader());
				}
			}
		}
	}

	/**
	 * 写数据
	 *
	 * @param dataSet
	 * @param exdt
	 * @param columnList
	 * @param wb
	 * @param sheet
	 * @param sheetTitle
	 * @param extend
	 * @throws SecurityException
	 * @throws NoSuchFieldException
	 * @throws IOException
	 */
	private static void writeExpDate(List<? extends Object> dataSet,
			ExportXmlDataEntity exdt, List<ExcelColumnEntity> columnList,
			Workbook wb, Sheet sheet,String sheetTitle, ExcelExtends extend)
			throws Exception {
		if (dataSet != null && dataSet.size() > 0 && exdt != null
				&& columnList != null && columnList.size() > 0) {
			Row row;
			Cell cell;
			int id = 1;
			CellStyle style = getExpDataStyle(wb, sheet);
			for (int i = 0; i < dataSet.size(); i++) {
				if (StringUtils.isNotBlank(sheetTitle)||StringUtils.isNotBlank(exdt.getTitle())) {
					row = sheet.createRow(exdt.getTitleRow() + 2 + i);
				} else {
					row = sheet.createRow(exdt.getTitleRow() + 1 + i);
				}
				if (exdt.getHasId()) {
					cell = row.createCell(exdt.getTitleCol());
					cell.setCellStyle(style);
					cell.setCellValue(id);
					id++;
				}
				for (int j = 0; j < columnList.size(); j++) {
					if (exdt.getHasId()) {
						cell = row.createCell(exdt.getTitleCol() + 1 + j);
					} else {
						cell = row.createCell(exdt.getTitleCol() + j);
					}
					cell.setCellStyle(style);
					List<String> cellValues = getExportCellValue(
							dataSet.get(i), columnList.get(j), j, extend, wb,
							sheet, row, cell);
					if (cellValues != null && cellValues.size() == 1) {
						cell.setCellValue(cellValues.get(0));
					} else if (cellValues != null && cellValues.size() > 1) {
						String cellValue = "";
						for (int k = 0; k < cellValues.size(); k++) {
							cellValue += cellValues.get(k) + "、";
						}
						cell.setCellValue(cellValue.substring(0, cellValue.length()-1));
					}
				}
			}
		}
	}

	/**
	 * 写图片
	 *
	 * @param wb
	 * @param sheet
	 * @param row
	 * @param cell
	 * @param picPath
	 * @throws IOException
	 */
	private static void writeExpPicture(Workbook wb, Sheet sheet, Row row,
			Cell cell, String picPath) throws IOException {
		if (StringUtils.isNotBlank(picPath)) {
			BufferedInputStream bis = new BufferedInputStream(
					new FileInputStream(picPath));
			byte[] buf = new byte[bis.available()];
			while ((bis.read(buf)) != -1) {
			}
			bis.close();
			writeExpPicture(wb, sheet, row, cell, buf);
		}
	}

	/**
	 * 写图片
	 *
	 * @param wb
	 * @param sheet
	 * @param row
	 * @param cell
	 * @param picByte
	 * @throws IOException
	 */
	private static void writeExpPicture(Workbook wb, Sheet sheet, Row row,
			Cell cell, byte[] picByte) throws IOException {
		if (picByte != null && picByte.length > 0) {
			CellStyle style = getExpPictureStyle(wb, sheet, row, cell);
			cell.setCellStyle(style);
			ClientAnchor anchor;
			if (wb instanceof HSSFWorkbook) {
				anchor = new HSSFClientAnchor(0, 0, 1023, 255,
						(short) cell.getColumnIndex(), row.getRowNum(),
						(short) cell.getColumnIndex(), row.getRowNum());
			} else {
				anchor = new XSSFClientAnchor(0, 0, 1023, 255,
						cell.getColumnIndex(), row.getRowNum(),
						cell.getColumnIndex() + 1, row.getRowNum() + 1);
			}
			anchor.setAnchorType(2);
			Drawing patriarch = sheet.createDrawingPatriarch();
			patriarch.createPicture(anchor,
					wb.addPicture(picByte, Workbook.PICTURE_TYPE_JPEG));
		}
	}

	/**
	 * 获得将要导出的单元格的值
	 *
	 * @param obj
	 * @param ec
	 * @param colIndex
	 * @param extend
	 * @param wb
	 * @param sheet
	 * @param row
	 * @param cell
	 * @return
	 * @throws SecurityException
	 * @throws NoSuchFieldException
	 * @throws IOException
	 */
	private static List<String> getExportCellValue(Object obj,
			ExcelColumnEntity ec, int colIndex, ExcelExtends extend,
			Workbook wb, Sheet sheet, Row row, Cell cell)
			throws Exception {
		if (ec != null) {
			List<String> cellValues = new ArrayList<String>();
			Map<String, String> dicn = ec.getMapping();
			if (obj instanceof Object[]) {
				Object[] obj1= (Object[])obj;
				int arrayIndex = ec.getArrayIndex();
				if(arrayIndex==0||arrayIndex>obj1.length){
					arrayIndex=colIndex+1;
				}
				return getCellValueFormArray(dicn, obj, cellValues, arrayIndex-1, wb,  sheet,  row,  cell);
			}
			int isPic = ec.getIsPicture();
			String reField = ec.getRltEntityField();
			String field = ec.getEntityField();
			Object value = getObjectFieldValue(obj, field);
			if (value != null) {
				String fieldType = obj.getClass().getDeclaredField(field)
						.getType().toString();
				// 此字段是list类型,里边放关联实体
				if (reField != null && fieldType.contains("List")) {
					List<Object> rltEntitys = (List) value;
					return getCollectionCellValues(reField, dicn, cellValues,
							rltEntitys);
				}
				// set
				if (reField != null && fieldType.contains("Set")) {
					Set<Object> rltEntitys = (Set) value;
					return getCollectionCellValues(reField, dicn, cellValues,
							rltEntitys);
				}
				// map
				if (reField != null && fieldType.contains("Map")) {
					return null;
				}
				// 数组
				if (reField != null && fieldType.contains("[")) {
					return null;
				}
				// 自定义类
				if (reField != null) {
					Object refvalue = getObjectFieldValue(value, reField);
					if (refvalue != null) {
						return getSingleCellValue(dicn, cellValues, refvalue);
					}
				}
				// byte[](数据库直接存放图片)
				if (reField == null
						&& (fieldType.contains("[B") || fieldType
								.contains("[Ljava.lang.Byte"))) {
					byte[] pictureByte = (byte[]) value;
					writeExpPicture(wb, sheet, row, cell, pictureByte);
					return null;
				}
				// 此字段可能是数值、布尔、字符、string、date、
				if (reField == null) {
					if (isPic == 1 && extend != null) {
						String fieldvalue = value.toString();
						if (StringUtils.isNotBlank(fieldvalue)) {
							String picPath = extend.getPicturePath(fieldvalue);
							writeExpPicture(wb, sheet, row, cell, picPath);
						}
						return null;
					}
					return getSingleCellValue(dicn, cellValues, value);
				}
			}
		}
		return null;
	}

	/**
	 * 从数组获取单元格的值
	 * @param dicn
	 * @param obj
	 * @param cellValues
	 * @param arrayIndex
	 * @param wb
	 * @param sheet
	 * @param row
	 * @param cell
	 * @return
	 * @throws Exception
	 */
	private static List<String> getCellValueFormArray(Map<String, String> dicn,
			Object obj, List<String> cellValues, int arrayIndex,Workbook wb, Sheet sheet, Row row, Cell cell) throws Exception {
		Object[] array = (Object[]) obj;
		if(array[arrayIndex] instanceof byte[]){
			writeExpPicture(wb, sheet, row, cell, (byte[])array[arrayIndex]);
			return cellValues;
		}
		if(array[arrayIndex] instanceof Map){
			Map<Object, Object> map=(Map)array[arrayIndex];
			for (Map.Entry entry : map.entrySet()) {
				if(entry.getKey() != null && entry.getValue() != null){
					cellValues.add(entry.getKey().toString()+":"+entry.getValue().toString());
				}
			}
			return cellValues;
		}
		if(array[arrayIndex] instanceof Collection){
			Collection c=(Collection)array[arrayIndex];
			for (Object obj1 : c) {
				if(obj1 != null){
					cellValues.add(obj1.toString());
				}
			}
			return cellValues;
		}
		if(array[arrayIndex] != null){
			String cellValue = getRealCellValue(array[arrayIndex].toString(), dicn);
			cellValues.add(cellValue);
		}
		return cellValues;
	}

	/**
	 * 对应字段是集合，获取要导出的集合值
	 *
	 * @param reField
	 * @param dicn
	 * @param cellValues
	 * @param rltEntitys
	 * @return
	 */
	private static List<String> getCollectionCellValues(String reField,
			Map<String, String> dicn, List<String> cellValues,
			Collection rltEntitys) {
		if (rltEntitys != null && rltEntitys.size() > 0) {
			for (Object rltObj : rltEntitys) {
				Object refValue = getObjectFieldValue(rltObj, reField);
				if (refValue != null) {
					String refvalue = refValue.toString();
					String cellValue = getRealCellValue(refvalue, dicn);
					cellValues.add(cellValue);
				}
			}
			return cellValues;
		}
		return null;
	}

	/**
	 * 对应字段是单值，获取此要导出的值
	 *
	 * @param dicn
	 * @param cellValues
	 * @param value
	 * @return
	 */
	private static List<String> getSingleCellValue(Map<String, String> dicn,
			List<String> cellValues, Object value) {
		String Value = value.toString();
		String cellValue = getRealCellValue(Value, dicn);
		cellValues.add(cellValue);
		return cellValues;
	}

	/**
	 * 获取真正要导出的值
	 *
	 * @param fieldValue
	 * @param dicn
	 * @return
	 */
	private static String getRealCellValue(String fieldValue,
			Map<String, String> dicn) {
		if (dicn != null && dicn.size() > 0) {
			for (Map.Entry<String, String> entry : dicn.entrySet()) {
				if (entry.getKey().equals(fieldValue)) {
					return entry.getValue();
				}
			}
		}
		return fieldValue;
	}

	/**
	 * 通过属性名反射获取对象的属性值
	 *
	 * @param obj
	 * @param field
	 * @return
	 */
	private static Object getObjectFieldValue(Object obj, String field) {
		try {
			if (obj != null && StringUtils.isNotBlank(field)) {
				String getMethodName = "get"
						+ field.substring(0, 1).toUpperCase()
						+ field.substring(1);
				Method m = obj.getClass().getMethod(getMethodName);
				Object value = m.invoke(obj);
				return value;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 获取ImportResult对象，此对象包含实体对象集、数组对象集和错误信息集
	 *
	 * @param imdt
	 * @param columnList
	 * @param sheet
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	private static ImportResult getImpResult(ImportXmlDataEntity imdt,
			List<ExcelColumnEntity> columnList, Sheet sheet)
			throws InstantiationException, IllegalAccessException,
			ClassNotFoundException {
		if (sheet.getLastRowNum() > 0) {
			List<Object> objs = new ArrayList<Object>();
			List<Object> rightObjs = new ArrayList<Object>();
			List<Object> errorObjs = new ArrayList<Object>();
			List<Object[]> objArrays = new ArrayList<Object[]>();
			List<Object[]> rightArrays = new ArrayList<Object[]>();
			List<Object[]> errorArrays = new ArrayList<Object[]>();
			List<String> rightRowNums = new ArrayList<String>();
			List<String> errorRowNums = new ArrayList<String>();
			List<String> errorMessages = new ArrayList<String>();
			int errorNum = createDataSet(imdt, columnList, sheet, rightObjs,
					errorObjs, rightArrays, errorArrays, rightRowNums, errorRowNums, errorMessages);
			objs.addAll(rightObjs);
			objs.addAll(errorObjs);
			objArrays.addAll(rightArrays);
			objArrays.addAll(errorArrays);
			ImportResult result = new ImportResult();
			result.setRowNum(sheet.getLastRowNum());
			result.setErrorNum(errorNum);
			result.setRightNum(sheet.getLastRowNum() - errorNum);
			result.setObjs(objs);
			result.setRightObjs(rightObjs);
			result.setErrorObjs(errorObjs);
			result.setObjArray(objArrays);
			result.setRightArrays(rightArrays);
			result.setErrorArrays(errorArrays);
			result.setRightRowNums(rightRowNums);
			result.setErrorRowNums(errorRowNums);
			result.setErrorMessages(errorMessages);
			result.setErrorMessagesNum(errorMessages.size());
			return result;
		}
		return null;
	}

	/**
	 * 由Excel表数据为对象数据集赋值，返回错误行数
	 *
	 * @param imdt
	 * @param columnList
	 * @param sheet
	 * @param rightObjs
	 * @param errorObjs
	 * @param rightArrays
	 * @param errorArrays
	 * @param rightRowNums
	 * @param errorRowNums
	 * @param errorMessages
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	private static int createDataSet(ImportXmlDataEntity imdt,
			List<ExcelColumnEntity> columnList, Sheet sheet, List<Object> rightObjs, List<Object> errorObjs,
			List<Object[]> rightArrays, List<Object[]> errorArrays, List<String> rightRowNums, List<String> errorRowNums, List<String> errorMessages)
			throws InstantiationException, IllegalAccessException,
			ClassNotFoundException {
		int errorNum = 0;
		for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
			Row row = sheet.getRow(i);
			if (columnList != null && columnList.size() > 0 && imdt != null
					&& row != null) {
				boolean flag = true;
				Object obj = null;
				Object[] objArray = new Object[columnList.size()];
				String className = imdt.getClassname();
				if (className != null) {
					obj = Class.forName(className).newInstance();
				}
				Map<Map<String, String>, Map<String, String>> rltEntityMap = new HashMap<Map<String, String>, Map<String, String>>();
				for (int j = 0; j < columnList.size(); j++) {
					Cell cell = row.getCell(j);
					String cellValue = getImportCellValue(cell);
					List<String> errorMessage = null;
					// 判断有验证类、验证方法即要进行数据检查
					if (imdt.getCheckClassName() != null
							&& columnList.get(j).getCheckMap() != null
							&& columnList.get(j).getCheckMap().size() > 0) {
						errorMessage = checkedImpCellValue(
								imdt.getCheckClassName(), cellValue,
								columnList.get(j), i + 1, j + 1);
					}
					if (errorMessage != null) {
						for (String error : errorMessage) {
							flag = false;
							errorMessages.add(error);
						}
					}
					// 赋值
					if (className != null) {
						setObjectValue(obj, objArray, cellValue,
								columnList.get(j), j + 1,
								columnList.size(), rltEntityMap);
					} else {
						setArrayValue(objArray, cellValue,
								columnList.get(j), j);
					}

				}
				if (!flag) {
					errorNum++;
					errorObjs.add(obj);
					errorArrays.add(objArray);
					errorRowNums.add(i+1+"");
				}else{
					rightObjs.add(obj);
					rightArrays.add(objArray);
					rightRowNums.add(i+1+"");
				}
			}
		}
		return errorNum;
	}

	/**
	 * 获取要导入excel的单元格的值
	 *
	 * @param cell
	 * @return
	 */
	private static String getImportCellValue(Cell cell) {
		String value = null;
		if (cell != null) {
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING:
				value = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					value = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
							.format(cell.getDateCellValue());
				} else {
					cell.setCellType(Cell.CELL_TYPE_STRING);
					value = cell.getStringCellValue();
				}
				break;
			case Cell.CELL_TYPE_FORMULA:
				switch (cell.getCachedFormulaResultType()) {
				case Cell.CELL_TYPE_STRING:
					value = cell.getStringCellValue();
					break;
				case Cell.CELL_TYPE_NUMERIC:
					if (DateUtil.isCellDateFormatted(cell)) {
						value = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
								.format(cell.getDateCellValue());
					} else {
						cell.setCellType(Cell.CELL_TYPE_STRING);
						value = cell.getStringCellValue();
					}
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					value = String.valueOf(cell.getBooleanCellValue());
					break;
				case Cell.CELL_TYPE_ERROR:
					value = "";
					break;
				default:
					value = cell.toString();
				}
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				value = String.valueOf(cell.getBooleanCellValue());
				break;
			case Cell.CELL_TYPE_BLANK:
				value = "";
				break;
			case Cell.CELL_TYPE_ERROR:
				value = "";
				break;
			default:
				value = cell.toString();
			}
			return value.trim();
		}
		return value;
	}

	/**
	 * 检查要导入的单元格值是否合法
	 *
	 * @param checkClassName
	 * @param cellValue
	 * @param ec
	 * @param row
	 * @param col
	 * @return
	 */
	private static List<String> checkedImpCellValue(String checkClassName,
			String cellValue, ExcelColumnEntity ec, int row, int col) {
		try {
			List<String> errors = new ArrayList<String>();
			Class clazz = Class.forName(checkClassName);
			Map<String, Map<String, String>> checkMap = ec.getCheckMap();
			for (Map.Entry<String, Map<String, String>> entry : checkMap
					.entrySet()) {
				String error = null;
				String methodName = entry.getKey().substring(0, entry.getKey().indexOf("@"));
				if (entry.getValue() != null && entry.getValue().size() > 0) {
					Class[] cla = new Class[entry.getValue().size()+1];
					Object[] obj = new Object[entry.getValue().size()+1];
					cla[0] = String.class;
					obj[0] = cellValue;
					int i = 1;
					for (Map.Entry<String, String> entry1 : entry.getValue()
							.entrySet()) {
						String paramType = entry1.getKey().substring(0, entry1.getKey().indexOf("@"));
						String paramValue= entry1.getValue();
						Object[] obj1 = getParamTypeArray(paramType,paramValue);
						if (obj1 != null) {
							cla[i] = (Class)obj1[0];
							obj[i] = obj1[1];
						}else{
							errors.add("验证方法"+methodName+"参数类型"+paramType+"不合法！");
							return errors;
						}
						i++;
					}
					Method method = clazz.getMethod(methodName, cla);
					error = (String) method.invoke(clazz.newInstance(), obj);
				} else {
					Method method = clazz.getMethod(methodName,String.class);
					error = (String) method.invoke(clazz.newInstance(),
							cellValue);
				}
				if (StringUtils.isNotBlank(error)) {
					String errorMessage = "第" + row + "行第" + col + "列有错误："
							+ error;
					errors.add(errorMessage);
				}
			}
			if (errors.size() > 0) {
				return errors;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 获取参数类型的class对象与参数值的数组
	 * @param paramType
	 * @param paramVlaue
	 * @return
	 * @throws ParseException
	 */
	private static Object[] getParamTypeArray(String paramType,String paramVlaue) throws ParseException {
		Object[] obj = new Object[2] ;
		if ("byte".equals(paramType)) {
			obj[0]=byte.class;
			obj[1]=Byte.parseByte(paramVlaue);
			return obj;
		}
		if ("Byte".equals(paramType)) {
			obj[0]=Byte.class;
			obj[1]=Byte.parseByte(paramVlaue);
			return obj;
		}
		if ("short".equals(paramType)) {
			obj[0]=short.class;
			obj[1]=Short.parseShort(paramVlaue);
			return obj;
		}
		if ("Short".equals(paramType)) {
			obj[0]=Short.class;
			obj[1]=Short.parseShort(paramVlaue);
			return obj;
		}
		if ("int".equals(paramType)) {
			obj[0]=int.class;
			obj[1]=Integer.parseInt(paramVlaue);
			return obj;
		}
		if ("Integer".equals(paramType)) {
			obj[0]=Integer.class;
			obj[1]=Integer.parseInt(paramVlaue);
			return obj;
		}
		if ("long".equals(paramType)) {
			obj[0]=long.class;
			obj[1]=Long.parseLong(paramVlaue);
			return obj;
		}
		if ("Long".equals(paramType)) {
			obj[0]=Long.class;
			obj[1]=Long.parseLong(paramVlaue);
			return obj;
		}
		if ("float".equals(paramType)) {
			obj[0]=float.class;
			obj[1]=Float.parseFloat(paramVlaue);
			return obj;
		}
		if ("Float".equals(paramType)) {
			obj[0]=Float.class;
			obj[1]=Float.parseFloat(paramVlaue);
			return obj;
		}
		if ("double".equals(paramType)) {
			obj[0]=double.class;
			obj[1]=Double.parseDouble(paramVlaue);
			return obj;
		}
		if ("Double".equals(paramType)) {
			obj[0]=Double.class;
			obj[1]=Double.parseDouble(paramVlaue);
			return obj;
		}
		if ("char".equals(paramType)) {
			obj[0]=char.class;
			obj[1]=paramVlaue.charAt(0);
			return obj;
		}
		if ("Character".equals(paramType)) {
			obj[0]=Character.class;
			obj[1]=paramVlaue.charAt(0);
			return obj;
		}
		if ("boolean".equals(paramType)) {
			obj[0]=boolean.class;
			obj[1]=Boolean.parseBoolean(paramVlaue);
			return obj;
		}
		if ("Boolean".equals(paramType)) {
			obj[0]=Boolean.class;
			obj[1]=Boolean.parseBoolean(paramVlaue);
			return obj;
		}
		if ("String".equals(paramType)) {
			obj[0]=String.class;
			obj[1]=paramVlaue;
			return obj;
		}
		if ("Date".equals(paramType)) {
			obj[0]=Date.class;
			obj[1]=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(paramVlaue);
			return obj;
		}
		return null;
	}

	/**
	 * 导入数据为实体字段或数组赋值
	 * @param obj
	 * @param objArray
	 * @param cellValue
	 * @param ec
	 * @param count
	 * @param size
	 * @param rltEntityMap
	 */
	private static void setObjectValue(Object obj, Object[] objArray,
			String cellValue, ExcelColumnEntity ec, int count, int size,
			Map<Map<String, String>, Map<String, String>> rltEntityMap) {
		try {
			String field = ec.getEntityField();
			String rltEntity = ec.getRltEntity();
			String rltEntityField = ec.getRltEntityField();
			Map<String, String> map = ec.getMapping();
			if (field != null && cellValue != null) {
				String param = getSetMethodParam(cellValue, map);
				objArray[count - 1] = param;
				if (rltEntity != null && rltEntityField != null) {// type：自定义类
					Map<String, String> rltClassMap = new HashMap<String, String>();
					rltClassMap.put(field, rltEntity);
					if (!rltEntityMap.containsKey(rltClassMap)) {
						Map<String, String> fieldMap = new HashMap<String, String>();
						fieldMap.put(rltEntityField, param);
						rltEntityMap.put(rltClassMap, fieldMap);
					} else {
						rltEntityMap.get(rltClassMap)
								.put(rltEntityField, param);
					}
				} else if (rltEntity == null && rltEntityField == null) {// type：普通类型
					invokeSetMethod(obj, field, param);
				}
				if (rltEntityMap.size() > 0 && count == size) {
					for (Map.Entry<Map<String, String>, Map<String, String>> entry : rltEntityMap
							.entrySet()) {
						for (Map.Entry<String, String> entry1 : entry.getKey()
								.entrySet()) {
							Object rltObj = Class.forName(entry1.getValue())
									.newInstance();
							for (Map.Entry<String, String> entry2 : entry
									.getValue().entrySet()) {
								invokeSetMethod(rltObj, entry2.getKey(),
										entry2.getValue());
							}
							invokeSetMethod(obj, entry1.getKey(), rltObj);
						}
					}
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 给数组赋值
	 *
	 * @param objArray
	 * @param cellValue
	 * @param ec
	 * @param colIndex
	 */
	private static void setArrayValue(Object[] objArray, String cellValue,
			ExcelColumnEntity ec, int colIndex) {
		Map<String, String> map = ec.getMapping();
		String param = getSetMethodParam(cellValue, map);
		objArray[colIndex] = param;
	}

	/**
	 * 获取对应set方法需传入的参数
	 *
	 * @param param
	 * @param dicn
	 * @return
	 */
	private static String getSetMethodParam(String param,
			Map<String, String> dicn) {
		if (dicn != null && dicn.size() > 0) {
			for (Map.Entry<String, String> entry : dicn.entrySet()) {
				if (entry.getValue().equals(param)) {
					return entry.getKey();
				}
			}
		}
		return param;
	}

	/**
	 * 反射执行相应set方法
	 *
	 * @param obj
	 * @param field
	 * @param param
	 * @throws Exception
	 */
	private static void invokeSetMethod(Object obj, String field, Object param)
			throws Exception {
		if (!("".equals(param.toString()))) {
			String setMethodName = "set" + field.substring(0, 1).toUpperCase()
					+ field.substring(1);
			Class clazz = obj.getClass();
			Class fieldType = clazz.getDeclaredField(field).getType();
			Method method = clazz.getMethod(setMethodName, fieldType);
			String fieldTypeStr = fieldType.toString();
			if ("byte".equals(fieldTypeStr)
					|| "class java.lang.Byte".equals(fieldTypeStr)) {
				String p=param.toString();
				p.replaceAll("\\s|\n|\t|\r", "");
				method.invoke(obj, Byte.parseByte(p));
				return;
			}
			if ("short".equals(fieldTypeStr)
					|| "class java.lang.Short".equals(fieldTypeStr)) {
				String p=param.toString();
				p.replaceAll("\\s|\n|\t|\r", "");
				method.invoke(obj, Short.parseShort(p));
				return;
			}
			if ("int".equals(fieldTypeStr)
					|| "class java.lang.Integer".equals(fieldTypeStr)) {
				String p=param.toString();
				p.replaceAll("\\s|\n|\t|\r", "");
				method.invoke(obj, Integer.parseInt(p));
				return;
			}
			if ("long".equals(fieldTypeStr)
					|| "class java.lang.Long".equals(fieldTypeStr)) {
				String p=param.toString();
				p.replaceAll("\\s|\n|\t|\r", "");
				method.invoke(obj, Long.parseLong(p));
				return;
			}
			if ("float".equals(fieldTypeStr)
					|| "class java.lang.Float".equals(fieldTypeStr)) {
				String p=param.toString();
				p.replaceAll("\\s|\n|\t|\r", "");
				method.invoke(obj, Float.parseFloat(p));
				return;
			}
			if ("double".equals(fieldTypeStr)
					|| "class java.lang.Double".equals(fieldTypeStr)) {
				String p=param.toString();
				p.replaceAll("\\s|\n|\t|\r", "");
				method.invoke(obj, Double.parseDouble(p));
				return;
			}
			if ("char".equals(fieldTypeStr)
					|| "class java.lang.Character".equals(fieldTypeStr)) {
				method.invoke(obj, param.toString().charAt(0));
				return;
			}
			if ("boolean".equals(fieldTypeStr)
					|| "class java.lang.Boolean".equals(fieldTypeStr)) {
				method.invoke(obj, Boolean.parseBoolean(param.toString()));
				return;
			}
			if ("class java.lang.String".equals(fieldTypeStr)) {
				method.invoke(obj, param.toString());
				return;
			}
			if ("class java.util.Date".equals(fieldTypeStr)) {
				method.invoke(obj, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
						.parse(param.toString()));
				return;
			}
			method.invoke(obj, param);
		}
	}

	/**
	 * 获取导出的Excel标题样式
	 *
	 * @param wb
	 * @param sheet
	 * @param count
	 * @param exdt
	 * @return
	 */
	private static CellStyle getExpTitleStyle(Workbook wb, Sheet sheet,
			int count, ExportXmlDataEntity exdt) {
		int startRow = exdt.getTitleRow();
		int startCol = exdt.getTitleCol();
		int endCol = startCol + count - 1;
		if (endCol > -1) {
			sheet.addMergedRegion(new CellRangeAddress(startRow, startRow,
					startCol, endCol));
		}
		sheet.setDefaultColumnWidth(15);
		CellStyle style = wb.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_CENTER);
		Font font = wb.createFont();
		font.setFontHeightInPoints((short) 20);
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style.setFont(font);
		return style;
	}

	/**
	 * 获取导出的Excel表头样式
	 *
	 * @param wb
	 * @param sheet
	 * @return
	 */
	private static CellStyle getExpHeaderStyle(Workbook wb, Sheet sheet) {
		sheet.setDefaultColumnWidth(15);
		CellStyle style = wb.createCellStyle();
		style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		Font font = wb.createFont();
		font.setColor(Font.COLOR_NORMAL);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style.setFont(font);
		return style;
	}

	/**
	 * 获取导出的Excel数据内容样式
	 *
	 * @param wb
	 * @param sheet
	 * @return
	 */
	private static CellStyle getExpDataStyle(Workbook wb, Sheet sheet) {
		sheet.setDefaultColumnWidth(15);
		CellStyle style = wb.createCellStyle();
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		Font font = wb.createFont();
		font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
		style.setFont(font);
		return style;
	}

	/**
	 * 获取导出的Excel图片样式
	 *
	 * @param wb
	 * @param sheet
	 * @param row
	 * @param cell
	 * @return
	 */
	private static CellStyle getExpPictureStyle(Workbook wb, Sheet sheet,
			Row row, Cell cell) {
		row.setHeightInPoints(100);
		sheet.setColumnWidth(cell.getColumnIndex(), (int) (35.7 * 100));
		CellStyle style = wb.createCellStyle();
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		return style;
	}
}
